package dao;

import java.math.BigDecimal;
import java.math.RoundingMode;
import java.sql.*;
import java.util.Objects;
import java.util.Vector;

import org.eclipse.jdt.annotation.NonNull;

import bean.Commodity;

public class CommodityDAO {
    private Connection conn;

    public CommodityDAO(@NonNull Connection conn) {
		this.conn = conn;
	}

	public boolean insert(@NonNull Commodity commodity) throws SQLException {
		PreparedStatement ps = conn.prepareStatement("INSERT INTO COMMODITIES VALUES(?,?,?,?,?)");
        ps.setString(1, commodity.getCommodityName());
        ps.setInt(2, commodity.getCategoryId());
        ps.setBigDecimal(3, commodity.getCostPrice());
        ps.setBigDecimal(4, commodity.getSalePrice());
        ps.setInt(5, commodity.getQuantity());
        boolean state = ps.executeUpdate() == 1;
        ps.close();
        return state;
    }

    public Commodity selectById(int id) throws SQLException {
        Commodity commodity = null;
        if (id < 1) {
            return commodity;
        }
        PreparedStatement ps = conn.prepareStatement("SELECT A.*,B.CATEGORY_NAME"
            + " FROM COMMODITIES AS A INNER JOIN CATEGORIES AS B"
            + " ON B.CATEGORY_ID=A.CATEGORY_ID AND COMMODITY_ID=?");
        ps.setInt(1, id);
        ResultSet rs = ps.executeQuery();
        if (rs.next()) {
            String name = rs.getString("COMMODITY_NAME");
            int categoryId = rs.getInt("CATEGORY_ID");
            BigDecimal costPrice = rs.getBigDecimal("COST_PRICE");
            BigDecimal salePrice = rs.getBigDecimal("SALE_PRICE");
            int quantity = rs.getInt("QUANTITY");
            String categoryName = rs.getString("CATEGORY_NAME");
            commodity = new Commodity(id, name, categoryId, costPrice, salePrice, quantity,  categoryName);
            rs.close();
        }
        rs.close();
        ps.close();
        return commodity;
    }

    public Vector<Commodity> selectByKeyword(String keyword, int categoryId) throws SQLException {
        Vector<Commodity> commodities = new Vector<>();
        boolean state1 = Objects.nonNull(keyword) && keyword.length() > 0;
        boolean state2 = categoryId > 0;
        boolean state = state1 && state2;
        String sql = "SELECT A.*,B.CATEGORY_NAME FROM COMMODITIES AS A INNER JOIN CATEGORIES AS B"
            + " ON B.CATEGORY_ID=A.CATEGORY_ID";
        String sql1 = " AND COMMODITY_NAME LIKE ?";
        String sql2 = " AND B.CATEGORY_ID=?";
        keyword = "%" + keyword + "%";
        PreparedStatement ps = conn.prepareStatement(sql);
        if (state) {
            sql += sql1 + sql2;
            ps = conn.prepareStatement(sql);
            ps.setString(1, keyword);
            ps.setInt(2, categoryId);
        } else if (state1) {
            sql += sql1;
            ps = conn.prepareStatement(sql);
            ps.setString(1, keyword);
        } else if (state2) {
            sql += sql2;
            ps = conn.prepareStatement(sql);
            ps.setInt(1, categoryId);
        }
        ResultSet rs = ps.executeQuery();
        while (rs.next()) {
            int id = rs.getInt("COMMODITY_ID");
            String name = rs.getString("COMMODITY_NAME");
            categoryId = rs.getInt("CATEGORY_ID");
            BigDecimal costPrice = rs.getBigDecimal("COST_PRICE");
            BigDecimal salePrice = rs.getBigDecimal("SALE_PRICE");
            int quantity = rs.getInt("QUANTITY");
            String categoryName = rs.getString("CATEGORY_NAME");
            Commodity commodity = new Commodity(id, name, categoryId, costPrice, salePrice, quantity, categoryName);
            commodities.addElement(commodity);
        }
        rs.close();
        ps.close();
        return commodities;
    }

    public int selectIdCountByCategoryId(int categoryId) throws SQLException {
        int count = -1;
        if (categoryId < 1) {
            return count;
        }
        PreparedStatement ps = conn.prepareStatement("SELECT COUNT(COMMODITY_ID) FROM COMMODITIES WHERE CATEGORY_ID=?");
        ps.setInt(1, categoryId);
        ResultSet rs = ps.executeQuery();
        if (rs.next()) {
            count = rs.getInt(1);
        }
        rs.close();
        ps.close();
        return count;
    }

    public boolean update(@NonNull Commodity commodity) throws SQLException {
    	PreparedStatement ps = conn.prepareStatement("SELECT COST_PRICE,QUANTITY FROM COMMODITIES"
	        + " WHERE COMMODITY_ID=?");
        ps.setInt(1, commodity.getCommodityId());
        ResultSet rs = ps.executeQuery();
        if (rs.next()) {
            BigDecimal costPrice = rs.getBigDecimal("COST_PRICE");
            int quantity = rs.getInt("QUANTITY");
            BigDecimal commoditiesCostPrice = costPrice.multiply(new BigDecimal(quantity));
            quantity = commodity.getQuantity();
            commoditiesCostPrice = commoditiesCostPrice.add(commodity.getCostPrice().multiply(new BigDecimal(quantity)));
            quantity += rs.getInt("QUANTITY");
            rs.close();
            ps.close();
            costPrice = commoditiesCostPrice.divide(new BigDecimal(quantity), 2, RoundingMode.HALF_UP);
            ps = conn.prepareStatement("UPDATE COMMODITIES SET COMMODITY_NAME=?,CATEGORY_ID=?,COST_PRICE=?,"
                + "SALE_PRICE=?,QUANTITY=? WHERE COMMODITY_ID=?");
            ps.setString(1, commodity.getCommodityName());
            ps.setInt(2, commodity.getCategoryId());
            ps.setBigDecimal(3, costPrice);
            ps.setBigDecimal(4, commodity.getSalePrice());
            ps.setInt(5, quantity);
            ps.setInt(6, commodity.getCommodityId());
            boolean state = ps.executeUpdate() == 1;
            return state;
        }
        ps.close();
        rs.close();
        return false;
    }

    public boolean delete(int id) throws SQLException {
        if (id < 1) {
            return false;
        }
        PreparedStatement ps = conn.prepareStatement("DELETE FROM COMMODITIES WHERE COMMODITY_ID=?");
        ps.setInt(1, id);
        boolean state = ps.executeUpdate() == 1;
        ps.close();
        conn.close();
        return state;
    }
}
